﻿using System;
using System.Data;
using System.Data.SQLite;
using System.IO;
using System.Text.RegularExpressions;
using System.Windows.Forms;

namespace DrawImage
{
    public partial class DataCRUD : Form
    {
        private int pageNum = 0;
        private int pageRecordCount = 5;
        private DataSet dataSet = null;

        public DataCRUD()
        {
            InitializeComponent();
            new ToolTip().SetToolTip(this.leftButton, "上一页");
            new ToolTip().SetToolTip(this.rightButton, "下一页");
            new ToolTip().SetToolTip(this.plusButton, "添加一条数据");
            new ToolTip().SetToolTip(this.minusButton, "删除记录");
            new ToolTip().SetToolTip(this.upButton, "修改记录");
            new ToolTip().SetToolTip(this.textButton, "从文件导入记录");
            new ToolTip().SetToolTip(this.refreshButton, "刷新");
        }

        private void DataRUD_Load(object sender, EventArgs e)
        {
            dataSet = GetData(0, pageRecordCount);
            AddData2DataGridView(dataSet.Tables[0], pageRecordCount);
        }

        private void AddData2DataGridView(DataTable table, int pRecordCount)
        {
            for (int i = 0; i < table.Rows.Count; ++i) {
                DataRow row = table.Rows[i];
                var rowNum = this.dataGridView1.Rows.Add(1);
                dataGridView1.Rows[rowNum].Cells[0].Value = i + (pageNum * pRecordCount) + 1;
                dataGridView1.Rows[rowNum].Cells[1].Value = row[0].ToString();
                dataGridView1.Rows[rowNum].Cells[2].Value = row[1].ToString();
                dataGridView1.Rows[rowNum].Cells[3].Value = row[2].ToString();
                dataGridView1.Rows[rowNum].Cells[4].Value = row[3].ToString();
                dataGridView1.Rows[rowNum].Cells[5].Value = row[4].ToString();
                dataGridView1.Rows[rowNum].Cells[6].Value = row[5].ToString();
                dataGridView1.Rows[rowNum].Cells[7].Value = row[6].ToString();
                dataGridView1.Rows[rowNum].Cells[8].Value = row[7].ToString();
                dataGridView1.Rows[rowNum].Cells[9].Value = row[8].ToString();
            }
        }

        private static DataSet GetData(int pNum, int pRecordCount)
        {
            var sql = @"select * from images limit @pRecordCount offset @offset";
            var parameter = new SQLiteParameter[]{
                new SQLiteParameter("@offset", pNum * pRecordCount),
                new SQLiteParameter("@pRecordCount", pRecordCount)
            };
            return DBHelperSQLite.Query(sql, parameter);
        }

        private static int InsertIntoDB(string name, string id, string longitude, string lantitude, string altitude, string height, string distance, string angle, string position)
        {
            int errCode = 0;
            if (id == "") {
                MessageBox.Show("编号不能为空！");
                return -1;
            }

            var sql = @"insert into images
                        values(  @name,
		                        @id,
                                @longitude,
                                @lantitude,
                                @altitude,
                                @height,
                                @distance,
                                @angle,
                                @position);";
            var parameters = new SQLiteParameter[]
            {
                new SQLiteParameter("@name",name),
                new SQLiteParameter("@id",id),
                new SQLiteParameter("@longitude",longitude),
                new SQLiteParameter("@lantitude",lantitude),
                new SQLiteParameter("@altitude",altitude),
                new SQLiteParameter("@height",height),
                new SQLiteParameter("@distance",distance),
                new SQLiteParameter("@angle",angle),
                new SQLiteParameter("@position",position)
            };
            try {
                var ds = DBHelperSQLite.Query(sql, parameters);
            }
            catch (Exception) {
                errCode = 1;
            }
            return errCode;
        }

        private static void DeleteData(string id)
        {
            string sql = @"delete from images where id = @id";
            var parameter = new SQLiteParameter("@id", id);
            DBHelperSQLite.Query(sql, parameter);
        }

        private void dataGridView1_CellMouseClick(object sender, DataGridViewCellMouseEventArgs e)
        {

        }

        private void leftButton_Click(object sender, EventArgs e)
        {
            rightButton.Enabled = true;
            --pageNum;
            if (pageNum <= 0) {
                pageNum = 0;
                leftButton.Enabled = false;
            }
            dataSet = GetData(pageNum, pageRecordCount);
            this.dataGridView1.Rows.Clear();
            AddData2DataGridView(dataSet.Tables[0], pageRecordCount);
        }

        private void rightButton_Click(object sender, EventArgs e)
        {
            leftButton.Enabled = true;
            ++pageNum;
            var tempDs = GetData(pageNum, pageRecordCount);
            if (tempDs.Tables[0].Rows.Count > 0) {
                dataSet = tempDs;
                this.dataGridView1.Rows.Clear();
                AddData2DataGridView(dataSet.Tables[0], pageRecordCount);
            }
            else {
                --pageNum;
                rightButton.Enabled = false;
            }
        }

        private void plusButton_Click(object sender, EventArgs e)
        {
            new DataEnter().ShowDialog();
        }

        private void minusButton_Click(object sender, EventArgs e)
        {
            if (dataSet == null) {
                return;
            }
            foreach (DataGridViewRow row in dataGridView1.SelectedRows) {
                DeleteData(row.Cells[2].Value.ToString().Trim());
            }
            refreshButton_Click(null, null);
        }

        private void upButton_Click(object sender, EventArgs e)
        {
            if (dataGridView1.SelectedRows.Count < 1)
                return;
            var row = dataGridView1.SelectedRows[0];
            new DataEnter(row).ShowDialog();
            refreshButton_Click(null, null);
        }

        private void textButton_Click(object sender, EventArgs e)
        {
            string filePath = "";
            var openFileDialog = new OpenFileDialog
            {
                Filter = "TXT 文件 (*.txt)|*.txt"
            };
            if (openFileDialog.ShowDialog() == DialogResult.OK) {
                filePath = openFileDialog.FileName;
            }
            else
                return;
            FileStream fileStream = new FileStream(filePath, FileMode.Open, FileAccess.Read, FileShare.Read);
            StreamReader streamReader = new StreamReader(fileStream);

            string line = null;
            int sucesCount = 0;
            int errorCount = 0;
            string errorIDs = "";
            while ((line = streamReader.ReadLine()) != null) {
                var fields = Regex.Replace(line, @"\s+", ",").Split(',');
                var name = fields[0].Trim();
                var id = fields[1].Trim();
                var longitude = fields[2].Trim();
                var lantitude = fields[3].Trim();
                var altitude = fields[4].Trim();
                var height = fields[5].Trim();
                var distance = fields[6].Trim();
                var angle = fields[7].Trim();
                var position = fields[8].Trim();
                var errCode = InsertIntoDB(name, id, longitude, lantitude, altitude, height, distance, angle, position);
                if (errCode != 0) {
                    ++errorCount;
                    errorIDs += id + ";";
                }
                else {
                    ++sucesCount;
                }
            }

            streamReader?.Dispose();
            fileStream?.Dispose();

            string message = $"录入成功：{sucesCount} 条\r\n录入失败：{errorCount} 条\r\n";
            if (errorCount > 0) {
                message += $"图片编号为：{errorIDs}";
            }
            MessageBox.Show(message);
        }

        private void refreshButton_Click(object sender, EventArgs e)
        {
            dataSet = GetData(pageNum, pageRecordCount);
            this.dataGridView1.Rows.Clear();
            AddData2DataGridView(dataSet.Tables[0], pageRecordCount);
        }
    }
}
